/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ru.staf.NetworkReportGenerator.V2;

import java.util.Map;
import java.util.Set;
import java.util.LinkedList;
import java.util.List;
import java.io.File;
import jxl.write.WritableWorkbook;
import jxl.write.WritableSheet;
import jxl.Workbook;
import jxl.write.WritableFont;
import jxl.write.WritableCellFormat;
import jxl.write.Label;
import jxl.CellView;

import java.io.IOException;
import jxl.write.WriteException;

/**
 *
 * @author aezhov
 */
public class ExcelTableOutput implements Output {

    List<Switch> switches = new LinkedList<Switch>();
    WritableWorkbook wbook = null;
    WritableSheet wsheet = null;
    WritableCellFormat centerCell = null;
    WritableCellFormat stdCell = null;
    WritableCellFormat stdCellGrey = null;
    WritableCellFormat stdCell_HL1 = null;
    WritableCellFormat stdCellGrey_HL1 = null;
    int posY = 0;

    public ExcelTableOutput(String filename) throws
            IOException, WriteException {
        System.out.println("Output filename is " + filename);
        wbook = Workbook.createWorkbook(new File(filename));
        wsheet = wbook.createSheet("Network Reporter v. 0.0.21", 0);

        WritableFont headerFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
        WritableFont hl1_Font = new WritableFont(WritableFont.ARIAL, 10);
        hl1_Font.setColour(jxl.format.Colour.BLUE);

        centerCell = new WritableCellFormat(headerFont);
        centerCell.setAlignment(jxl.format.Alignment.CENTRE);

        stdCell = new WritableCellFormat();
        stdCell.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

        stdCellGrey = new WritableCellFormat();
        stdCellGrey.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
        stdCellGrey.setBackground(jxl.format.Colour.YELLOW);

        stdCell_HL1 = new WritableCellFormat(hl1_Font);
        stdCell_HL1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);

        stdCellGrey_HL1 = new WritableCellFormat(hl1_Font);
        stdCellGrey_HL1.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
        stdCellGrey_HL1.setBackground(jxl.format.Colour.YELLOW);
    }

    public void addAll(Set<Switch> switches) {
        this.switches.addAll(switches);
    }


    public void produce() throws Exception {
        for (Switch sw : switches) {
            System.out.println("ExcelTableOutput.produce: switch "
                    + sw.getName());
            Map<String, Node> map = sw.getConnections();
            openNewTable(sw.getName(), sw.getSysDescr());

            WritableCellFormat currentFormat = stdCellGrey;
            WritableCellFormat currentFormat_HL1 = stdCellGrey_HL1;

            for (String port : map.keySet()) {
                Node node = map.get(port);
                int posX = 0;
                
                if (node != null) {
                    System.out.print("ExcelTableOutput.produce:     Port: "
                            + port + " neighbour:" + node.getName() + " isUp "
                            + sw.isPortUp(port) + " neighbourType: "
                            + node.getClass().getSimpleName());

                    System.out.println();
                    boolean isPortUp = sw.isPortUp(port);
                    
                    Label lPortName = new Label(posX++, posY, port, currentFormat);
                    Label lPortUp = new Label(posX++, posY, "Нет", currentFormat);
                        if (sw.isPortUp(port))
                            lPortUp.setString("Да");
                    Label lLinkSpeed = new Label(posX++, posY,
                            ifSpeedStr(sw.getPortSpeed(port)), currentFormat);
                    Label lVlans = new Label(posX++, posY,
                            sw.getVlanString(port), currentFormat);
                     Label lIP = new Label(posX++, posY,
                            isPortUp ? node.getIpAddresses() : " ",
                            currentFormat_HL1);
                    Label lOctInAVG = new Label(posX++, posY,
                            isPortUp ? toMbitsStr(sw.getOctetsPerSec(port, false, "AVERAGE")) : " ",
                            currentFormat);
                    Label lOctInMAX = new Label(posX++, posY,
                            isPortUp ? toMbitsStr(sw.getOctetsPerSec(port, false, "MAX")) : " ",
                            currentFormat_HL1);
                    Label lOctInMIN = new Label(posX++, posY,
                            isPortUp ? toMbitsStr(sw.getOctetsPerSec(port, false, "MIN")) : " ",
                            currentFormat);
                    Label lOctOutAVG = new Label(posX++, posY,
                            isPortUp ? toMbitsStr(sw.getOctetsPerSec(port, true, "AVERAGE")) : " ",
                            currentFormat);
                    Label lOctOutMAX = new Label(posX++, posY,
                            isPortUp ? toMbitsStr(sw.getOctetsPerSec(port, true, "MAX")) : " ",
                            currentFormat_HL1);
                    Label lOctOutMIN = new Label(posX++, posY,
                            isPortUp ? toMbitsStr(sw.getOctetsPerSec(port, true, "MIN")) : " ",
                            currentFormat);
                    Label lErrIn = new Label(posX++, posY,
                            isPortUp ? toNumStr(sw.getErrorsPerSec(port, false, "TOTAL")) : " ",
                            currentFormat);
                    Label lErrOut = new Label(posX++, posY,
                            isPortUp ? toNumStr(sw.getErrorsPerSec(port, true, "TOTAL")) : " ",
                            currentFormat);
                    Label lDiscardIn = new Label(posX++, posY,
                            isPortUp ? toNumStr(sw.getDiscardsPerSec(port, false, "TOTAL")) : " ",
                            currentFormat);
                    Label lDiscardOut = new Label(posX++, posY,
                            isPortUp ? toNumStr(sw.getDiscardsPerSec(port, true, "TOTAL")) : " ",
                            currentFormat);
                    Label lMAC = new Label(posX++, posY, " ", currentFormat);
                    if (node instanceof LeafDevice)
                        lMAC.setString(node.getMacAddress());
                   
                    Label lNbSw = new Label(posX++, posY,
                            isPortUp ? node.getName() : " ",
                            currentFormat);
                    Label lModel = new Label(posX++, posY,
                            isPortUp ? node.getVendor() + " " + node.getModel() : " ",
                            currentFormat);
                    Label lVendor = new Label(posX++, posY,
                            node.getSpecificNotice(), currentFormat);
                 

                    wsheet.addCell(lPortName);
                    wsheet.addCell(lPortUp);
                    wsheet.addCell(lLinkSpeed);
                    wsheet.addCell(lVlans);
                    wsheet.addCell(lOctInAVG);
                    wsheet.addCell(lOctInMAX);
                    wsheet.addCell(lOctInMIN);
                    wsheet.addCell(lOctOutAVG);
                    wsheet.addCell(lOctOutMAX);
                    wsheet.addCell(lOctOutMIN);
                    wsheet.addCell(lErrIn);
                    wsheet.addCell(lErrOut);
                    wsheet.addCell(lDiscardIn);
                    wsheet.addCell(lDiscardOut);
                    wsheet.addCell(lMAC);
                    wsheet.addCell(lIP);
                    wsheet.addCell(lVendor);
                    wsheet.addCell(lModel);
                    wsheet.addCell(lNbSw);
                    
                    currentFormat = currentFormat == stdCellGrey ? stdCell : stdCellGrey;
                    currentFormat_HL1 = currentFormat_HL1 == stdCellGrey_HL1 ? stdCell_HL1 : stdCellGrey_HL1;
                    posY++;
                }
            }

            posY += 2;
        }

        System.out.println("CLOSING WORKBOOK");
        wbook.write();
        wbook.close();
    }

    void openNewTable(String tableName, String sysDescr) throws WriteException {
        int posX = 0;

        Label labelH = new Label(0, posY, tableName.toUpperCase() + "  (" +
                sysDescr + ")", centerCell);
        wsheet.mergeCells(0, posY, 18, posY);
        wsheet.addCell(labelH);
        posY++;

        Label labelh1 = new Label(5, posY, "Мегабит/сек ВХОД", centerCell);
        Label labelh2 = new Label(8, posY, "Мегабит/сек ВЫХОД", centerCell);
        Label labelh3 = new Label(11, posY, "Ошибок всего", centerCell);
        Label labelh4 = new Label(13, posY, "Отказ обсл всего", centerCell);

        wsheet.mergeCells(5, posY, 7, posY);
        wsheet.mergeCells(8, posY, 10, posY);
        wsheet.mergeCells(11, posY, 12, posY);
        wsheet.mergeCells(13, posY, 14, posY);

        posY++;
        posX = 0;

        Label labelh5 = new Label(posX++, posY, "Порт", centerCell);
        Label labelhUp = new Label(posX++, posY, "Подл.", centerCell);
        Label labelh55 = new Label(posX++, posY, "Ск. подкл.", centerCell);
        Label labelvlans = new Label(posX++, posY, "VLANы", centerCell);
        Label labelhIP = new Label(posX++, posY, "IP addr", centerCell);
        Label labelh6 = new Label(posX++, posY, "AVG", centerCell);
        Label labelh7 = new Label(posX++, posY, "MAX", centerCell);
        Label labelh8 = new Label(posX++, posY, "MIN", centerCell);
        Label labelh9 = new Label(posX++, posY, "AVG", centerCell);
        Label labelh10 = new Label(posX++, posY, "MAX", centerCell);
        Label labelh11 = new Label(posX++, posY, "MIN", centerCell);
        Label labelh12 = new Label(posX++, posY, "Вход", centerCell);
        Label labelh13 = new Label(posX++, posY, "Выход", centerCell);
        Label labelh14 = new Label(posX++, posY, "Вход", centerCell);
        Label labelh15 = new Label(posX++, posY, "Выход", centerCell);
        Label labelhMAC = new Label(posX++, posY, "MAC addr", centerCell);
        Label labelhVendor = new Label(posX++, posY, "Имя", centerCell);
        Label labelhModel = new Label(posX++, posY, "Модель", centerCell);
        Label labelNbSw = new Label(posX++, posY, "Примечания", centerCell);
        
        CellView cv = new CellView();
        cv.setAutosize(true);
        wsheet.setColumnView(12, cv);
        wsheet.setColumnView(13, cv);
        wsheet.setColumnView(14, cv);
        wsheet.setColumnView(15, cv);
        wsheet.setColumnView(16, cv);
        wsheet.setColumnView(17, cv);
        wsheet.setColumnView(18, cv);
        wsheet.setColumnView(1, cv);
        wsheet.setColumnView(2, cv);
        wsheet.setColumnView(3, cv);
        wsheet.setColumnView(4, cv);

        wsheet.addCell(labelhUp);
        wsheet.addCell(labelh1);
        wsheet.addCell(labelh2);
        wsheet.addCell(labelh3);
        wsheet.addCell(labelvlans);
        wsheet.addCell(labelh4);
        wsheet.addCell(labelh5);
        wsheet.addCell(labelh55);
        wsheet.addCell(labelh6);
        wsheet.addCell(labelh7);
        wsheet.addCell(labelh8);
        wsheet.addCell(labelh9);
        wsheet.addCell(labelh10);
        wsheet.addCell(labelh11);
        wsheet.addCell(labelh12);
        wsheet.addCell(labelh13);
        wsheet.addCell(labelh14);
        wsheet.addCell(labelh15);
        wsheet.addCell(labelhMAC);
        wsheet.addCell(labelhIP);
        wsheet.addCell(labelhVendor);
        wsheet.addCell(labelhModel);
        wsheet.addCell(labelNbSw);

        posY++;
    }


    String toMbitsStr(double valueInBytes) {
        double mbits = (valueInBytes * 8) / 1e6;
        String result = String.format(" %.3f ", mbits);
        return result;
    }


    String toNumStr(double packets) {
        if (packets < 0)
            return " ";
        
        String result = String.format(" %.2f", packets);
        return result;
    }


    String ifSpeedStr(Integer speedStr) {
        int ifSpeed = speedStr.intValue();
        if (ifSpeed == 100000000)
            return "100 Mb/sec";
        else if (ifSpeed == 1000000000)
            return "1 Gbit/sec";
        else {
            return (new Integer(ifSpeed / 1000000).toString() + " Mb/sec");
        }
    }


    String getIpString(Set<String> ips) {
        if (ips == null)
            return "n/a";

        String result = "";
        String sep = "";
        for (String ip : ips) {
            result += sep + ip;
            sep = ",";
        }

        return result;
    }
}
